-- =====================================================

CREATE OR REPLACE PACKAGE BODY ZOEDEVOPS.ZOEPKG_UTILITY

-- Created in 2019.06.18 by polarbears
-- Copyright (c) 20xx, CHINA and/or affiliates.
-- All rights reserved.
--	Name:
-- 		zoepkg_utility.pkb
--	Description:
-- 		基础工具包
--  Relation:
--      建在所有其它包之前
--	Notes:
--		基本注意事项
--	修改 - （年-月-日） - 描述
--
--

AS

    FUNCTION SPLIT_SQL(iv_sql IN VARCHAR2,iv_split_char VARCHAR2)
    RETURN GTV_SQL
    AS
        lv_sql_i NUMBER;
        ltv_sql  GTV_SQL;
        ln_split_char_count NUMBER;
    BEGIN
        lv_sql_i := 1;
        ln_split_char_count := LENGTH(TRANSLATE(iv_sql,iv_split_char||iv_sql,iv_split_char));
        FOR i IN 1..ln_split_char_count
        LOOP
            IF i=1 THEN
                ltv_sql(i) := SUBSTR(iv_sql,1,instr(iv_sql,iv_split_char,1,i)-1);
            ELSE
                ltv_sql(i) := SUBSTR(iv_sql,instr(iv_sql,iv_split_char,1,i-1)+1,instr(iv_sql,iv_split_char,1,i)-instr(iv_sql,iv_split_char,1,i-1)-1);
            END IF;
        END LOOP;
        RETURN ltv_sql;
    END SPLIT_SQL;

 

-- ===================================================
-- 枚举Oracle自身产品用户列表
-- ===================================================
FUNCTION GET_ORACLE_USER RETURN zoett_db_object_list AS
	lt_oracle_user zoett_db_object_list := zoett_db_object_list();
BEGIN
	lt_oracle_user.extend;
	lt_oracle_user(lt_oracle_user.count) := 'SYS';
	lt_oracle_user.extend;
	lt_oracle_user(lt_oracle_user.count) := 'SYSTEM';
	lt_oracle_user.extend;
	lt_oracle_user(lt_oracle_user.count) := 'ANONYMOUS';
	lt_oracle_user.extend;
	lt_oracle_user(lt_oracle_user.count) := 'APEX_030200';
	lt_oracle_user.extend;
	lt_oracle_user(lt_oracle_user.count) := 'APEX_PUBLIC_USER';
	lt_oracle_user.extend;
	lt_oracle_user(lt_oracle_user.count) := 'APPQOSSYS';
	lt_oracle_user.extend;
	lt_oracle_user(lt_oracle_user.count) := 'AUDSYS';
	lt_oracle_user.extend;
	lt_oracle_user(lt_oracle_user.count) := 'CTXSYS';
	lt_oracle_user.extend;
	lt_oracle_user(lt_oracle_user.count) := 'DBSNMP';
	lt_oracle_user.extend;
	lt_oracle_user(lt_oracle_user.count) := 'DIP';
	lt_oracle_user.extend;
	lt_oracle_user(lt_oracle_user.count) := 'DVF';
	lt_oracle_user.extend;
	lt_oracle_user(lt_oracle_user.count) := 'DVSYS';
	lt_oracle_user.extend;
	lt_oracle_user(lt_oracle_user.count) := 'EXFSYS';
	lt_oracle_user.extend;
	lt_oracle_user(lt_oracle_user.count) := 'FLOWS_FILES';
	lt_oracle_user.extend;
	lt_oracle_user(lt_oracle_user.count) := 'GSMADMIN_INTERNAL';
	lt_oracle_user.extend;
	lt_oracle_user(lt_oracle_user.count) := 'GSMCATUSER';
	lt_oracle_user.extend;
	lt_oracle_user(lt_oracle_user.count) := 'GSMUSER';
	lt_oracle_user.extend;
	lt_oracle_user(lt_oracle_user.count) := 'LBACSYS';
	lt_oracle_user.extend;
	lt_oracle_user(lt_oracle_user.count) := 'MDDATA';
	lt_oracle_user.extend;
	lt_oracle_user(lt_oracle_user.count) := 'MDSYS';
	lt_oracle_user.extend;
	lt_oracle_user(lt_oracle_user.count) := 'MGMT_VIEW';
	lt_oracle_user.extend;
	lt_oracle_user(lt_oracle_user.count) := 'OJVMSYS';
	lt_oracle_user.extend;
	lt_oracle_user(lt_oracle_user.count) := 'OLAPSYS';
	lt_oracle_user.extend;
	lt_oracle_user(lt_oracle_user.count) := 'ORACLE_OCM';
	lt_oracle_user.extend;
	lt_oracle_user(lt_oracle_user.count) := 'ORDDATA';
	lt_oracle_user.extend;
	lt_oracle_user(lt_oracle_user.count) := 'ORDPLUGINS';
	lt_oracle_user.extend;
	lt_oracle_user(lt_oracle_user.count) := 'ORDSYS';
	lt_oracle_user.extend;
	lt_oracle_user(lt_oracle_user.count) := 'OUTLN';
	lt_oracle_user.extend;
	lt_oracle_user(lt_oracle_user.count) := 'OWBSYS';
	lt_oracle_user.extend;
	lt_oracle_user(lt_oracle_user.count) := 'OWBSYS_AUDIT';
	lt_oracle_user.extend;
	lt_oracle_user(lt_oracle_user.count) := 'SCOTT';
	lt_oracle_user.extend;
	lt_oracle_user(lt_oracle_user.count) := 'SI_INFORMTN_SCHEMA';
	lt_oracle_user.extend;
	lt_oracle_user(lt_oracle_user.count) := 'SPATIAL_CSW_ADMIN_USR';
	lt_oracle_user.extend;
	lt_oracle_user(lt_oracle_user.count) := 'SPATIAL_WFS_ADMIN_USR';
	lt_oracle_user.extend;
	lt_oracle_user(lt_oracle_user.count) := 'SYSBACKUP';
	lt_oracle_user.extend;
	lt_oracle_user(lt_oracle_user.count) := 'SYSDG';
	lt_oracle_user.extend;
	lt_oracle_user(lt_oracle_user.count) := 'SYSKM';
	lt_oracle_user.extend;
	lt_oracle_user(lt_oracle_user.count) := 'WMSYS';
	lt_oracle_user.extend;
	lt_oracle_user(lt_oracle_user.count) := 'XDB';
	lt_oracle_user.extend;
	lt_oracle_user(lt_oracle_user.count) := 'XS$NULL';
	lt_oracle_user.extend;
	lt_oracle_user(lt_oracle_user.count) := 'SYSRAC';
	lt_oracle_user.extend;
	lt_oracle_user(lt_oracle_user.count) := 'REMOTE_SCHEDULER_AGENT';
	lt_oracle_user.extend;
	lt_oracle_user(lt_oracle_user.count) := 'DBSFWUSER';
	lt_oracle_user.extend;
	lt_oracle_user(lt_oracle_user.count) := 'SYS$UMF';
	lt_oracle_user.extend;
	lt_oracle_user(lt_oracle_user.count) := 'GGSYS';
	lt_oracle_user.extend;
	lt_oracle_user(lt_oracle_user.count) := 'SYSMAN';
	lt_oracle_user.extend;
	lt_oracle_user(lt_oracle_user.count) := 'APEX_180200';
    
	RETURN lt_oracle_user;
END GET_ORACLE_USER;

FUNCTION GET_REMOTE_DB_LINK(iv_object_id VARCHAR2) 
RETURN VARCHAR2
AS
    lv_db_link  VARCHAR2(128);
BEGIN
    SELECT DB_LINK_NAME INTO lv_db_link FROM ZOEDEVOPS.DVP_PROJ_NODE_DB_LINKS WHERE db_id# = iv_object_id;
    RETURN lv_db_link;
END GET_REMOTE_DB_LINK;

PROCEDURE SYNC_TABLE_DEFINE(iv_owner IN VARCHAR2, iv_table_name IN VARCHAR2, iv_sync_mode IN VARCHAR2,iv_db_link IN VARCHAR2 )
AS
    lv_error_return VARCHAR2(4000);
    lv_error_type ZOEDEVOPS.DVP_ERROR_LOG.ERROR_TYPE%TYPE;
    lv_error_info ZOEDEVOPS.DVP_ERROR_LOG.ERROR_INFO%TYPE;
    lv_owner    VARCHAR2(128);
    lv_object_name  VARCHAR2(128);
    lv_sql_type VARCHAR2(64);
    lv_sql  VARCHAR2(32767);
    ltv_sql  GTV_SQL;
    ln_sql_i NUMBER;
    lv_sql_json VARCHAR2(32767);
BEGIN
    -- 设置程序名上下文
    ZOEDEVOPS.ZOEPRC_SET_DEVOPS_CONTEXT('CURRENT_PROGRAM','ZOEDEVOPS.ZOEPKG_UTILITY.SYNC_TABLE_DEFINE');
    IF iv_sync_mode = 'CREATE' THEN
        SELECT OWNER,OBJECT_NAME,SOURCE_TYPE,TEXT 
        INTO lv_owner,lv_object_name,lv_sql_type,lv_sql
        FROM ZOEDEVOPS.DVP_SOURCE
            WHERE OWNER = iv_owner AND OBJECT_NAME = iv_table_name;
        IF lv_sql_type = 'DDL' THEN
            lv_sql := replace(lv_sql,chr(10),' ');
            lv_sql := replace(lv_sql,chr(13),' ');
            ltv_sql := SPLIT_SQL(lv_sql,';');
            FOR i IN 1..ltv_sql.COUNT
            LOOP
                lv_sql_json := '{"SQL_TYPE":"DDL",';
                lv_sql_json := lv_sql_json ||'"SQL_TEXT":';
                lv_sql_json := lv_sql_json ||'"'|| ltv_sql(i) ||'"}';
                --DBMS_OUTPUT.PUT_LINE(ltv_sql(i));
                ZOEDEVOPS.ZOEPRC_IF_REMOTE_EXEC_SQL(lv_sql_json,iv_db_link);
            END LOOP;
        END IF;
    ELSIF iv_sync_mode = 'REPLACE'  THEN
        lv_sql := 'CREATE OR REPLACE ';
    ELSIF iv_sync_mode = 'DROP'  THEN
        lv_sql := 'DROP TABLE ';
    END IF;
    COMMIT;
    EXCEPTION  
        WHEN OTHERS THEN  
            lv_error_type := 'PLSQL';  
            lv_error_info := '{"ERROR_TYPE":"'||lv_error_type||'",';  
            lv_error_info := lv_error_info || '"ERROR_CONTENT":';  
            lv_error_info := lv_error_info || '{"ERROR_PROGRAM":"'||SYS_CONTEXT('ZOE_DEVOPS_CONTEXT','CURRENT_PROGRAM')||'",';  
            lv_error_info := lv_error_info || '"ERROR_MESSAGE":"'||SQLERRM||'",';  
            lv_error_info := lv_error_info || '"ERROR_SQL":"'||TRUNC(lv_sql,2000)||'"}}';  
            lv_error_return := ZOEDEVOPS.ZOEFUN_WERROR_LOG(lv_error_info); 
        ROLLBACK;
END SYNC_TABLE_DEFINE;

PROCEDURE SYNC_DICT_DATA(iv_owner IN VARCHAR2, iv_table_name IN VARCHAR2, iv_sync_mode IN VARCHAR2, iv_db_link IN VARCHAR2)
AS
    lv_error_return VARCHAR2(4000);
    lv_error_type ZOEDEVOPS.DVP_ERROR_LOG.ERROR_TYPE%TYPE;
    lv_error_info ZOEDEVOPS.DVP_ERROR_LOG.ERROR_INFO%TYPE;
    lv_sql      VARCHAR2(4000);
BEGIN
    -- 设置程序名上下文
    ZOEDEVOPS.ZOEPRC_SET_DEVOPS_CONTEXT('CURRENT_PROGRAM','ZOEDEVOPS.ZOEPKG_UTILITY.SYNC_DICT_DATA');
    IF iv_sync_mode = 'APPEND' THEN
        lv_sql := 'INSERT INTO '||iv_owner||'.'||iv_table_name||'@'||iv_db_link|| ' SELECT * FROM '||iv_owner||'.'||iv_table_name;
    ELSIF iv_sync_mode = 'REPLACE'  THEN
        lv_sql := 'MEGRE INTO ';
    ELSIF iv_sync_mode = 'TRUNCATE'  THEN
        lv_sql := 'TRUNCATE TABLE ';
    END IF;
    EXECUTE IMMEDIATE lv_sql;
    COMMIT;
    EXCEPTION  
        WHEN OTHERS THEN  
            lv_error_type := 'PLSQL';  
            lv_error_info := '{"ERROR_TYPE":"'||lv_error_type||'",';  
            lv_error_info := lv_error_info || '"ERROR_CONTENT":';  
            lv_error_info := lv_error_info || '{"ERROR_PROGRAM":"'||SYS_CONTEXT('ZOE_DEVOPS_CONTEXT','CURRENT_PROGRAM')||'",';  
            lv_error_info := lv_error_info || '"ERROR_MESSAGE":"'||SQLERRM||'",';  
            lv_error_info := lv_error_info || '"ERROR_SQL":"'||TRUNC(lv_sql,2000)||'"}}';  
            lv_error_return := ZOEDEVOPS.ZOEFUN_WERROR_LOG(lv_error_info); 
        ROLLBACK;
END SYNC_DICT_DATA;

END ZOEPKG_UTILITY;
/
